Lookerの「行数表示制限5000件」の壁を超える

先ずはSQLより始めよ

モダンデータスタック(MDS)

#Looker

たまちゃん

2023.10.23

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

大阪オフィスの玉井です。

まず最初に一番大事なことをお伝えしておくと、この記事のタイトルは凄く盛っています。昇天ペガサスくらい盛ってます。残念ながら壁を超えることはできません。

その代わりといっては何ですが、Looker5000件問題に対する対策方法をご紹介します。

「Looker5000件問題」とは

上記を読んでもらうのが一番間違いないのですが、簡単に説明すると、Lookerでは、何らかのデータを可視化するとき、一気に表示できる行数（データポイント）は5000件が限界となっています。5000件以上を表示することができません。

Looker上で、取得結果が5000件を超える可視化をしようとした場合、以下のようなアラートが表示されます。

こちら、Lookerというサービスの性能がどうのこうのというより、「表示するWebブラウザ側のこと」を考えた末の仕様と思われます。というのも、Webブラウザでストレスなく表示できる限界が5000件くらいだからです。仮に、Google Cloud本社に行って「頼むから5000件の限界を解除してください！！」と土下座を行い、限界突破してもらったとしても、5000件以上を一気に可視化しようとした際、Webブラウザ自体が固まるor落ちる可能性が高いと思います。なので、この限界を超えようとすること自体、ナンセンスなのです。

解決方法はPagenation（ページング）

5000件が表示の限界である以上、ここは発想を変えて「全データを5000件ずつ順繰りに表示していく」という方向で行きます。

んまあ、ちょっと検索すると、公式に説明している記事が出てくるんですけどね…。

分かる人はこの記事を読めばスッと終わります。が、この記事では、上記の方法をもう少しじっくり説明していきたいと思います。

SQLから考える

LookMLで何かを解決しようとするとき、まずは「最終的にLookerに実行させるSQLから考える」のが良いとされています（すみません、筆者が勝手に思っているだけです）。

SnowflakeやGoogle BigQueryなど、ほとんどのDWH（DB）には、SQLで使える機能としてLIMITとOFFSETというものがあります。これを使うと、取得するデータの範囲を指定することができます。

BigQueryでちょこっと検証してみる

BigQueryのサンプルデータに対して、LIMITを使用してみます。LIMIT 20としているので、（どれだけデータがあろうとも）このクエリで表示するのは20件までとなります。

OFFSETを使用すると「表示を開始する件数の位置」を指定することができます。以下では、LIMIT 10に加えて、OFFSET 10としています。これは「実際に取得できる件数の11件目から、10件だけ表示する」という指定になります。

1枚目のスクショと比べるとわかりやすいと思います。IDの昇順に並べた結果の11件目（display_nameがGeoff Dalgas）から10件だけが表示されています。

LIMITとOFFSETを駆使すればページングができる

LIMITとOFFSETを使うことで、実際の取得結果から、好きな部分だけを表示できることがわかりました。

ここまでくれば、勘のいい人は分かると思います。LIMITとOFFSETの値を（Exploreから）動的に変更できるようにすれば、Looker上で5000件ずつページングできるのです！

実際にやってみた

上記のCommunityにある記事のコードを参考（というかほとんどそのまま）にし、以下のViewを作成しました。

view: users_page_nation {

  derived_table: {
    sql: select *
           from `bigquery-public-data.stackoverflow.users`
          limit {% parameter count_number %}
         offset {{ count_number._parameter_value | times: start_number._parameter_value | minus: count_number._parameter_value }}
    ;;
  }

  parameter: count_number {
    label: "取得件数"
    type: number
  }

  parameter: start_number {
    label: "開始ページ"
    type: number
  }
  
  dimension: id {
    primary_key: yes
    type: number
    sql: ${TABLE}.id ;;
  }
  
  dimension: display_name {
    type: string
    sql: ${TABLE}.display_name ;;
  }
  
  measure: count {
    type: count
  }

}

ポイントは2つのparameter（「開始ページ」と「取得件数」）です。このparameterに数値（ページングを開始したいページ目と取得する件数）を入れることで、任意のページング処理が実行された派生テーブルができます。

`offset`の部分のLiquidについて

上記の派生テーブルにはparameterが組み込まれています。limit {% parameter count_number %}は、parameterに指定した値がそのまま代入されるだけなので、わかりやすいと思います。

しかし、offset {{ count_number._parameter_value | times: start_number._parameter_value | minus: count_number._parameter_value }}については、パッと見ただけだと何のこっちゃ？という感じですね。

これを式として表現すると以下のようになります。

「取得件数（count_number）」 × 「開始ページ目（start_number）」 - 「取得件数（count_number）」

例えば「5000件ずつ表示するとして、それの3ページ目を取得したい」とします。その場合、LIMITは5000ですが、OFFSETは「5000件ずつ表示した時の3ページ目の最初」となります。その場合、3ページ分の件数から1ページ分を引く…「5000×3-5000 」…つまり10000を指定する必要があります。このシンプルな掛け算をLiquidで実装すると、上記のコードになります。

LiquidにはFiltersという機能があります。これは渡した値（パラメータ）に対して、色々な処理をかけることができるというものです。|をつなげることで、複数の処理を連続で実行することもできます（左から右にいくイメージ）。

今回のoffsetのところでは、timesとminusという2つの処理が記述されています。意味としてはそのまんま「乗算」と「減算」です。まず、渡された「取得件数（count_number）」に対して「開始ページ目（start_number）」を掛けて、その結果から「取得件数（count_number）」を引いています。結果的に、前述した式と同じ内容になっていると思います。

Explore上で生成されたSQLを確認した結果がこちら。ちゃんとなってますね。